#!/usr/bin/env python3
# coding: utf-8
"""
2024-02-05 harrydeep@wardyan.top 
参照jfinal Activerecord 的Db
"""

from DBUtils.PooledDB import PooledDB
## from dbutils.pooled_db import PooledDB          #, SharedDBConnection
from top.wardyan.core.wdb.core import DBPool
from top.wardyan.core.wdb.core.vo import *
# from loguru import logger
## from top.wardyan.utils.mylog import *
from top.wardyan.core.utils.mylog import *

# class Db :

#     def use(self, config : str) :
#         """
#         使用数据源
#         """
#         pass

#     def 


## 主数据源
MAINCONFIG = DBPool.MAINCONFIG

## 列出存储过程的参数
SQL_LIST_PROC_PARAMS = """
SELECT	c.name AS xProcName
		, CASE WHEN LEFT(p.name, 1) = '@' THEN RIGHT(p.name, LEN(p.name) - 1) ELSE p.name END AS xParamName
		, t.name AS xTypeName
		, p.parameter_id AS xParamIdx		-- 参数序号
		, p.max_length AS xMaxLength		-- 长度
		, p.is_output AS xIsOutput			-- 输出参数
		, p.is_nullable AS xIsNullable		-- 可为空
		--, p.has_default_value AS xHasDefaultValue	-- 是否有默认值
		--, p.default_value AS xDefaultValue	-- 默认值
FROM sys.procedures AS c (NOLOCK) 
LEFT JOIN sys.parameters AS p (NOLOCK) ON p.object_id = c.object_id
LEFT JOIN sys.types AS t (NOLOCK) ON t.system_type_id = p.system_type_id AND t.user_type_id = p.user_type_id
WHERE c.name = %s
"""




















def use(config : str) -> PooledDB:
    """
    使用数据源. 默认使用主数据源.
    问题: 导致全局使用同一个数据源
    """
    rs : PooledDB = None
    if config in DBPool.DBCONFIGS.keys() :
        rs = DBPool.DBCONFIGS.get(config)
    else :
        rs = DBPool.DBCONFIGS.get(MAINCONFIG)
    ## 检测是否已经连接到数据库,如果没有连接到数据库,则连接
    return rs

## 不定参数:

def getcursor(config : str = MAINCONFIG) :
    cursor = None
    db = use(config=config).connection()
    cursor = db.cursor(as_dict=True)
    return db, cursor

def backcursor(db, cursor) :
    cursor.close()
    db.close()



def find(sql : str, params = (), config : str = MAINCONFIG) :
    """
    只返回单行结果
    """
    # db = use(config=config).connection()
    # cursor = db.cursor(as_dict=True)
    # cursor.execute(sql, params)
    # rs = cursor.fetchone()
    # cursor.close()
    # db.close()
    rs = None
    db, cursor = getcursor(config=config)
    try :
        cursor.execute(sql, params)
        rs = cursor.fetchone()
    except BaseException as e :
        logger.error(e)
    finally :
        backcursor(db, cursor)
    return rs
"""
def list(sql : str, params = (), config : str = MAINCONFIG) :
    ###返回多行结果
    ### 不能用这个. 跟数据类型重名了
    rs = None
    db, cursor = getcursor(config=config)
    try :
        cursor.execute(sql, params)
        rs = cursor.fetchall()
    except BaseException as e :
        pass
    finally :
        backcursor(db, cursor)
    return rs
"""
def exec(sql : str, params = (), config : str = MAINCONFIG) :
    """
    执行,不返回结果. 没限制是过程或者语句
    没有修改的能否提交事务?
    """
    rs = None
    db, cursor = getcursor(config=config)
    try :
        cursor.execute(sql, params)
        db.commit()
    except BaseException as e :
        logger.error("执行SQL错误:{},配置:{}", sql, config)
        logger.error(e)
        ## throws(e)
        raise e
    finally :
        backcursor(db, cursor)





def exec_notran(sql : str, params = (), config : str = MAINCONFIG) :
    """
    执行,不返回结果. 没限制是过程或者语句
    没有修改的能否提交事务?
    """
    rs = None
    db, cursor = getcursor(config=config)
    try :
        cursor.execute(sql, params)
    except BaseException as e :
        logger.error(e)
        ## throws(e)
        raise e
    finally :
        backcursor(db, cursor)

def query(sql : str, params = (), config : str = MAINCONFIG) :
    """
    执行并返回结果集. 没限制是过程或者语句. 同list
    """
    ## return list(sql=sql, params=params, config=config)
    rs = None
    db, cursor = getcursor(config=config)
    try :
        ##logger.info("Db query : \n%s\n%s", sql, params)
        ## logger.info("Db query : \n{sql}\n{params}", sql, params)
        ## 2024-02-16 暂时只能记录到这个
        # logger.debug(sql)
        cursor.execute(sql, params)
        rs = cursor.fetchall()
        db.commit()
    except BaseException as e :
        logger.error(e)
    finally :
        backcursor(db, cursor)
    return rs

def getprocparams(name : str, config : str = MAINCONFIG) -> list :
    """
    查询存储过程的参数
    Args:
        name (str): 过程名

    Returns:
        list: 参数列表
    """
    rs = []
    ps = query(sql = SQL_LIST_PROC_PARAMS, params = (name), config = config )
    if ps is not None and len(ps) > 0 :
        for p in ps :
            t = WType(p["xProcName"],p["xParamName"],p["xTypeName"],p["xParamIdx"],p["xMaxLength"],p["xIsOutput"],p["xIsNullable"])
            rs.append(t)
    return rs

## 文本 / 类文本
TYPES_STR = ("text","uniqueidentifier","ntext","varchar","char","nchar","nvarchar","xml","sysname")
## 整型
TYPES_INT = ("int","tinyint","smallint","bigint")
TYPES_FLOAT = ("real","money","float","decimal","smallmoney","numeric")
TYPES_DATETIME = ("date","datetime","timestamp","datetime2","datetimeoffset","smalldatetime")
TYPES_BIT = ("bit")

def __get_type_by_typename__(typename : str) -> int :
    type = 0        ## 数据类型: 0文本 1整型 2浮点 3日期时间 4布尔
    if typename in TYPES_STR :
        ## 文本 / 类文本
        type = 0
    elif typename in TYPES_INT :
        type = 1
    elif typename in TYPES_FLOAT :
        type = 2
    elif typename in TYPES_DATETIME :
        type = 3
    elif typename in TYPES_BIT :
        type = 4
    else :
        type = 0
    return type

def __build_proc_exec_str__ (name : str, outputparam = True, config : str = MAINCONFIG) :
    """
    生成存储过程调用语句.第一个版本先不缓存,后续版本再优化?

    Args:
        name (str): 存储过程名

    Returns:
        str: 存储过程调用语句
    """
    ps = getprocparams(name=name,config=config)
    if ps is None :
        ps = []
    vs = []
    outputs = []
    declares = []
    for s in ps :
        p : WType = s
        ## typename = p.typeName
        paramname = p.paramName
        if not paramname.startswith("@") :
            paramname = "@" + paramname
        v = ""
        v += paramname + "="
        type = __get_type_by_typename__(p.typeName)
        
        if p.isOutput :
            param = "@t" + p.paramName + " "
            v += param + " OUTPUT"
            if type == 0 or type == 3:
                param += "NVARCHAR(MAX)"
            elif type == 1 or type == 4:
                param += "BIGINT"
            elif type == 2 :
                param += "FLOAT"
            else :
                param += "BIT"
            declares.append(param)
            ot = "@t" + p.paramName + " AS " + p.paramName
            outputs.append(ot)
            
        else :
            if type == 0 or type == 3:
                v += "%s"
            elif type == 1 or type == 4:
                v += "%d"
            elif type == 2 :
                v += "%f"
            else :
                v += "%s"
        vs.append(v)
        #vs.append("\n")

    rs = ""
    if outputparam and declares is not None and len(declares) > 0 :
        rs += "DECLARE " + ",".join(declares) + "\n"
    rs += "exec " + name + " " + "\n,".join(vs)
    if outputparam and outputs is not None and len(outputs) > 0 :
        rs += "\nSELECT " + ",".join(outputs)
    return rs, ps

def sp_getkeyvalue(tablename : str, field : str = "sID",  config = MAINCONFIG) :
    rs : int = None
    rs = proc("sp_getkeyvalue", params = {"pTableName":tablename, "pKeyField":field}, outputparam = True, config = config)
    return rs


def yp_getid(tablename : str, field : str = "sID", cnt : int = 1,  config = MAINCONFIG) :
    rs : int = None
    rs = proc("yp_getid", params = {"pTableName":tablename, "pField":field, "pQty":cnt}, outputparam = True, config = config)["rResult"]
    return rs

def proc(name : str, params = {}, outputparam = False, config = MAINCONFIG) :
    """
    执行存储过程, 并返回数据集或返回输出参数 (同时只能输出一个)
    TODO
    需要先查出存储过程信息并生成调用
    """
    ## ps:过程定义的的参数
    sql, ps = __build_proc_exec_str__(name=name, outputparam=outputparam, config=config)
    ## print(sql)
    aps = []
    if ps is None :
        ps = []
    keys = params.keys()
    ## values = params.values()
    for s in ps :
        p : WType = s
        paramname = p.paramName
        if paramname.startswith("@") :
            paramname = paramname[1:]

        type = __get_type_by_typename__(p.typeName)
        value = None
        if type == 0 or type == 3:
            value = ""
        elif type == 1 or type == 4:
            value = 0
        elif type == 2 :
            value = 0.0
        else :
            value = ""
        if paramname in keys :
            value = params[paramname]
        ## aps[paramname]=value
        aps.append(value)
    rs = query(sql=sql, params = tuple(aps), config=config)
    # if rs is None :
    #     rs = []
    if outputparam and rs is not None :
        if len(rs) > 0 :
            rs = rs[0]
    return rs




if __name__ == '__main__':
    #print(find("select * from kt_device where rDept_ID = %d", (20130), MAINCONFIG))
    ## print(find("select * from kt_device", {}, "ERPDB"))
    # exec("select * from kt_device")
    # exec("select * from kt_device")
    # exec("select * from kt_device")
    # exec("select * from kt_device")
    # print("1234")
    ## print(list("select * from kt_device"))
    # print(list("update kt_device set xName = xName"))
    ## print(__build_proc_exec_str__("kp_rfid_get_value"))
    # print("11234")
    
    # print(proc("kp_rfid_getcardinfo", {"pCard":20251800200051}, True))
    # print(sp_getkeyvalue("kt_device", "sID"))

    print(yp_getid("yt_check","sID"))
    
